/**
 * 
 */
package com.disys.examples;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.disys.database.connectivity.DBConnectionManager;
import com.disys.model.Rec140ERWOTCLOC;

/**
 * @author Ubaid Raja
 * 
 */
public class ConnectMSSQLServer {
	public static void main(String args[]) {
		
		ConnectMSSQLServer obj = new ConnectMSSQLServer(); 
				obj.callStoreProcedure();
//		Connection conn = null;
//        String dbName = "Peoplesoft";
//        String serverip="disys-dev01";
//		String dbName = "dbmigration";
//        String serverip="127.0.0.1";
//        String serverport="1433";
//        String url = "jdbc:sqlserver://"+serverip+"\\SQLEXPRESS;databaseName="+dbName+"";
//        System.out.println(url);
//        Statement stmt = null;
//        ResultSet result = null;
//        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String databaseUserName = "faheem";
//        String databasePassword = "faheem";
//        try {
//            Class.forName(driver).newInstance();
//            conn = DriverManager.getConnection(url, databaseUserName, databasePassword);
//            stmt = conn.createStatement();
//            result = null;
//            String pa,us;
//            result = stmt.executeQuery("select * from ldap_users");
//
//            while (result.next()) {
//                us=result.getString("uid");
//                pa = result.getString("ROLE");              
//                System.out.println(us+"  "+pa);
//            }
//
//            conn.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
		
	}
	
	
	public Connection getConnection(){
//		String dbName = "dbmigration";
//        String serverip="127.0.0.1";
//        String serverport="1433";
//        String url = "jdbc:sqlserver://"+serverip+"\\SQLEXPRESS;databaseName="+dbName+"";
//        System.out.println(url);
//        Statement stmt = null;
//        ResultSet result = null;
//        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String databaseUserName = "faheem";
//        String databasePassword = "faheem";
//        try {
//            Class.forName(driver).newInstance();
//            Connection conn = DriverManager.getConnection(url, databaseUserName, databasePassword);
//            return conn;
//        }catch(Exception e){
//        	e.printStackTrace();
//        }
//        return null;
		Connection con = DBConnectionManager
				.getConnection(DBConnectionManager.SQLSERVER);
		return con;
	}
	
	public void callStoreProcedure(){
		ResultSet rs = null;
		PreparedStatement cs=null;
		Connection conn=getConnection();
		

		try {
			//cs = conn.prepareCall("{call sp_record140}");
			cs=conn.prepareStatement("exec sp_record140");
			boolean result = cs.execute();
			if(result){
				System.out.println("Successfully imported");
			}
			//cs.closeOnCompletion();
//		    cs.setEscapeProcessing(true);
//		    cs.setQueryTimeout(90);

//		    cs.setString(1, "valueA");

//		    cs.setString(2, "valueB");

//		    cs.setString(3, "0418");

		    //commented, because no need to register parameters out!, I got results from the resultset. 
		    //cs.registerOutParameter(1, Types.VARCHAR);
		    //cs.registerOutParameter(2, Types.VARCHAR);

			//cs.executeQuery();
		    //rs = cs.executeQuery();
//		    ArrayList<Rec140ERWOTCLOC> listaObjectX = new ArrayList<Rec140ERWOTCLOC>();
//		    while (rs.next()) {
//		    	//Rec140ERWOTCLOC obj = new Rec140ERWOTCLOC();
//		    	//obj.setTci_id(rs.getString("SETID"));
//		    	System.out.println(rs.getString("LOCATION"));
//		    	System.out.println(rs.getString("SETID"));
//			}
//		    System.out.println("LIst size is : "+listaObjectX.size());
		}catch(Exception e){
			e.printStackTrace();
		}
//		    while (rs.next()) {
//
//		        ObjectX to = new ObjectX();
//		        to.setFecha(rs.getString(1));
//		        to.setRefId(rs.getString(2));
//		        to.setRefNombre(rs.getString(3));
//		        to.setUrl(rs.getString(4));
//
//		        listaObjectX.add(to);
//
//		    }
//		    return listaObjectX;
//		     } catch (SQLException se) {
//		        System.out.println("Error al ejecutar SQL"+ se.getMessage());
//		        se.printStackTrace();
//		        throw new IllegalArgumentException("Error al ejecutar SQL: " + se.getMessage());
//
//		    } finally {
//
//		        try {
//
//		            rs.close();
//		            cs.close();
//		          con.close();
//
//		        } catch (SQLException ex) {
//		            ex.printStackTrace();
//		        }
//		    }
//	}
	}
}
